package com.zwd.cases.utils;

import jxl.Sheet;
import jxl.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description Excel文件处理类
 * @Author zhengwd
 * @Date 2022/5/13 16:24
 **/
public class ExcelUtil {

    public static void main(String[] args) {
        // 少量Excel数据处理，多量数据处理请参考TxtUtil
        doExcelForSQLBuildTest();
    }

    /**
     * 批量处理Excel数据，用户生成相关MySQL语句（方式一）
     *  作用：有时候数据需要恢复，少量数据恢复可以使用该方法
     *  弊端：该方法只能使用后缀xls格式的文件，且由于Excel有行数限制，处理的数据量有限
     */
    private static void doExcelForSQLBuildTest() {
        // 方式一：直接添加到自己的桌面，复制绝对路径测试
//        String filePath = "C:\\Users\\86178\\Desktop\\zwd1.xls";
        String filePath = "C:\\Users\\86178\\Desktop\\code.xls";
        File file = new File(filePath);

        // 方式二：使用项目中的测试Excel文件
//        String contextPath = getContextPath();
//        contextPath = contextPath.replace("target/test-classes/", "");
//        String filePath = contextPath + "src/test/java/com/zwd/cases/demo/file/test/zwd1.xls";
//        // 输出：/D:/workspace/projects/cases/src/test/java/com/zwd/cases/demo/file/test/zwd1.xls
//        File file = new File(filePath);

        /*
        输出
            outStr:
            Update food set food_name = '苹果' where food_id = '1' and food_operation <> 'D';
            Update food set food_name = '香蕉' where food_id = '2' and food_operation <> 'D';
            Update food set food_name = '西瓜' where food_id = '3' and food_operation <> 'D';
        */
//        readExcel(file);


        /*
        输出
            outStr:
            INSERT INTO `charge`.`dict_data` (`id`, `type`, `name`, `code`, `sort`, `ctime`) VALUES
            (1, '2', '防火门#红外', '1', 1, SYSDATE()),
            (2, '2', '水浸', '2', 2, SYSDATE()),
            (3, '2', '火警', '3', 3, SYSDATE())
            ;
        */
        readExcel3(file);
    }

    public static List<Map<String, Object>> readExcel3(File file) {
        StringBuffer outStr = new StringBuffer();
        InputStream is = null;
        List<Map<String, Object>> outerList = new ArrayList<>();
        StringBuffer str = new StringBuffer();
        int count = 1;
        int countData = 0;

        try {
            // 创建输入流，读取Excel
            is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            Workbook wb = Workbook.getWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();

            for (int index = 0; index < sheet_size; index++) {
                // 每个页签创建一个Sheet对象
                Sheet sheet = wb.getSheet(index);
                // sheet.getRows()返回该页的总行数
                for (int i = 0; i < sheet.getRows(); i++) {
                    StringBuffer strCh = new StringBuffer();
                    // sheet.getColumns()返回该页的总列数
                    int len = sheet.getColumns();

                    int countAdd = 0;
                    for (int j = 0; j < len; j++) {
                        String cellinfo = sheet.getCell(j, i).getContents();
//                        if(cellinfo.isEmpty()){
//                            continue;
//                        }
                        if (j == 0) {
                            strCh.append(" update device set device_code = '" + cellinfo + "' ");
                            countAdd++;
//                            continue;
                        }
                        if (j == 1) {
                            if (CommonUtil.strIsEmptyOrNull(cellinfo)) {
                                continue;
                            }
                            strCh.append(" where mac = '" + cellinfo + "';\n");
                            countAdd++;
//                            continue;
                        }
                    }
                    count++;
                    if (countAdd == 2) {
                        str.append(strCh);
                        countData++;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("str: ");
            System.out.println(str.toString());
            System.out.println("countData: " + countData);
        }
        return outerList;
    }

    /**
     * 去读Excel的方法readExcel，该方法的入口参数为一个File对象
     * @param file
     * @return
     */
    public static List<Map<String, Object>> readExcel2(File file) {
        StringBuffer outStr = new StringBuffer();
        int count = 1;
        int sort = 0;
        InputStream is = null;
        List<Map<String, Object>> outerList = new ArrayList<>();
        StringBuffer str = new StringBuffer("INSERT INTO `charge`.`dict_data` (`id`, `type`, `name`, `code`, `sort`, `ctime`) VALUES ");
        try {
            // 创建输入流，读取Excel
            is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            Workbook wb = Workbook.getWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();

            int oldType = 2;
            int newType = 0;
            for (int index = 0; index < sheet_size; index++) {
                // 每个页签创建一个Sheet对象
                Sheet sheet = wb.getSheet(index);
                // sheet.getRows()返回该页的总行数
                for (int i = 0; i < sheet.getRows(); i++) {
                    Map<String, Object> map = new HashMap<>();
                    // sheet.getColumns()返回该页的总列数
                    int len = sheet.getColumns();

                    str.append("(" + count + ",");
                    for (int j = 0; j < len; j++) {
                        String cellinfo = sheet.getCell(j, i).getContents();
                        if(cellinfo.isEmpty()){
                            continue;
                        }
                        if (j == 0) {
                            newType = Integer.parseInt(cellinfo);
                            str.append(" '" + cellinfo + "',");
                            continue;
                        }
                        if (j == 1) {
                            str.append(" '" + cellinfo + "',");
                            continue;
                        }
                        if (j == 2) {
                            str.append(" '" + cellinfo + "',");
                            continue;
                        }
                    }
                    sort++;
                    if (oldType != newType) {
                        sort = 1;
                    }
                    str.append(sort + ", SYSDATE()),");
                    count++;
                    oldType = newType;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("str: ");
            System.out.println(str.toString());
        }
        return outerList;
    }

    /**
     * 去读Excel的方法readExcel，该方法的入口参数为一个File对象
     * @param file
     * @return
     */
    public static List<Map<String, Object>> readExcel(File file) {
        StringBuffer outStr = new StringBuffer();
        int count = 0;
        InputStream is = null;
        List<Map<String, Object>> outerList = new ArrayList<>();
        try {
            // 创建输入流，读取Excel
            is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            Workbook wb = Workbook.getWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
            for (int index = 0; index < sheet_size; index++) {
                // 每个页签创建一个Sheet对象
                Sheet sheet = wb.getSheet(index);
                // sheet.getRows()返回该页的总行数
                for (int i = 0; i < sheet.getRows(); i++) {
                    Map<String, Object> map = new HashMap<>();
                    // sheet.getColumns()返回该页的总列数
                    int len = sheet.getColumns();
                    StringBuffer str = new StringBuffer("Update food set food_name = '");
                    for (int j = 0; j < len; j++) {
                        String cellinfo = sheet.getCell(j, i).getContents();
                        if(cellinfo.isEmpty()){
                            continue;
                        }
                        if (j == 0) {
                            str.append(cellinfo);
                            str.append("' where food_id = '");
                            continue;
                        }
                        if (j == 1) {
                            str.append(cellinfo);
                            str.append("' and food_operation <> 'D';\n");
                            continue;
                        }
                    }
                    // 第一行为标题，不添加
                    if (i != 0) {
                        outStr.append(str);
                        count++;
                    }
                }
                return outerList;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("outStr: ");
            System.out.println(outStr.toString());
        }
        return outerList;
    }

    /**
     * 获取上下文路径
     * @return
     */
    private static String getContextPath() {
        return ExcelUtil.class.getResource("/").getPath();
    }
}
